package rjeutsky.database.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import rjeutsky.database.interfaces.CRUDDomainRepository;
import rjeutsky.database.interfaces.Repository;
import rjeutsky.models.Payment;
import rjeutsky.models.Publication;
import rjeutsky.models.Subscription;
import rjeutsky.models.User;
import rjeutsky.models.UserRole;
import sun.reflect.generics.reflectiveObjects.NotImplementedException;

public class JDBCRepository implements Repository {

	private static final String ADD_ROLE_TO_USER_SQL = "INSERT INTO UserUserRole (UserId, RoleId) VALUES (?, ?)";
	private static final String GET_USER_BY_LOGIN_SQL = "SELECT Id, Login, Password, Salt FROM User WHERE Login=?";
	private static final String GET_USER_ROLES_SQL = "SELECT UserRole.Id, UserRole.Name FROM UserUserRole JOIN UserRole ON UserUserRole.RoleId = UserRole.Id WHERE UserId = ?";
	private static final String GET_UNPAYED_SUBSCRIPTIONS_SQL = "SELECT Id, UserId FROM Subscription WHERE Id NOT IN (SELECT Subscription.Id FROM Subscription JOIN Payment ON Payment.SubscriptionId = Subscription.Id)";
	private static final String GET_PREDICTED_COST_SQL = "SELECT SUM(Price) FROM Publication WHERE Id IN (%1$s)";
	private static final String ADD_PUBLICATION_TO_SUBSCRIPTION = "INSERT INTO PublicationSubscription (PublicationId, SubscriptionId) VALUES (?, ?)";
	private static final String GET_PUBLICATION_BY_READER = "SELECT DISTINCT Publication.Id, Publication.Name, Publication.Price FROM Publication JOIN PublicationSubscription ON Publication.Id = PublicationSubscription.PublicationId JOIN Subscription ON Subscription.Id = PublicationSubscription.SubscriptionId WHERE Subscription.UserId = ?";
	private static final String GET_SUBSCRIPTION_PUBLICATIONS = "SELECT Publication.Id, Publication.Name, Publication.Price FROM Publication JOIN PublicationSubscription ON Publication.Id = PublicationSubscription.PublicationId WHERE SubscriptionId = ?";
	private static final String GET_PAYMENT_AMOUNT = "SELECT SUM(Publication.Price) FROM Publication JOIN PublicationSubscription ON Publication.Id = PublicationSubscription.PublicationId WHERE PublicationSubscription.SubscriptionId = ?";
	private static final String PROCESS_PAYMENT = "INSERT INTO Payment (SubscriptionId, PayedSum) VALUES (?, ?)";
	private static final String GET_SUBSCRIPTION_PAYMENTS_COUNT = "SELECT COUNT(*) FROM Payment JOIN Subscription ON Payment.SubscriptionId = Subscription.Id WHERE Subscription.Id = ?";
	
	private PreparedStatement addRoleToUserStatement;
	private PreparedStatement getUserByLoginStatement;
	private PreparedStatement getUserRolesStatement;
	private PreparedStatement getUnpayedSubscriptions;
	private PreparedStatement getPublicationsByReader;
	private PreparedStatement addPublicationToSubscription;
	private PreparedStatement getSubscriptionPublications;
	private PreparedStatement getPaymentAmount;
	private PreparedStatement processPayment;
	private PreparedStatement subscriptionPaymentsCount;
	private Statement statement;
	
	private CRUDDomainRepository<UserRole> userRoleRepository;
	private CRUDDomainRepository<User> userRepository;
	private CRUDDomainRepository<Publication> publicationRepository;
	private CRUDDomainRepository<Subscription> subscriptionRepository;
	
	public JDBCRepository(Connection connection) throws SQLException {
		userRoleRepository = new UserRoleDomainRepository(connection);
		userRepository = new UserDomainRepository(connection);
		publicationRepository = new PublicationDomainRepository(connection);
		subscriptionRepository = new SubscriptionDomainRepository(connection);
		
		addRoleToUserStatement = connection.prepareStatement(ADD_ROLE_TO_USER_SQL);
		getUserByLoginStatement = connection.prepareStatement(GET_USER_BY_LOGIN_SQL);
		getUserRolesStatement = connection.prepareStatement(GET_USER_ROLES_SQL);
		getUnpayedSubscriptions = connection.prepareStatement(GET_UNPAYED_SUBSCRIPTIONS_SQL);
		addPublicationToSubscription = connection.prepareStatement(ADD_PUBLICATION_TO_SUBSCRIPTION);
		getPublicationsByReader = connection.prepareStatement(GET_PUBLICATION_BY_READER);
		getSubscriptionPublications = connection.prepareStatement(GET_SUBSCRIPTION_PUBLICATIONS);
		getPaymentAmount = connection.prepareStatement(GET_PAYMENT_AMOUNT);
		processPayment = connection.prepareStatement(PROCESS_PAYMENT);
		subscriptionPaymentsCount = connection.prepareStatement(GET_SUBSCRIPTION_PAYMENTS_COUNT);
		
		statement = connection.createStatement();
	}
	
	@Override
	public CRUDDomainRepository<Payment> getPaymentRepository() {
		// TODO Auto-generated method stub
		throw new NotImplementedException();
	}

	@Override
	public CRUDDomainRepository<Publication> getPublicationRepository() {
		return publicationRepository;
	}

	@Override
	public List<Publication> getPublicationsByReader(User reader) throws SQLException {
		List<Publication> publications = new LinkedList<Publication>();
		getPublicationsByReader.setLong(1, reader.getId());
		ResultSet resultSet = getPublicationsByReader.executeQuery();
		while (resultSet.next()) {
			Publication publication = new Publication();
			publication.setId(resultSet.getLong(1));
			publication.setName(resultSet.getString(2));
			publication.setPrice(resultSet.getLong(3));
			publications.add(publication);
		}
		
		return publications;
		
	}

	@Override
	public CRUDDomainRepository<User> getReaderRepository() {
		return userRepository;
	}

	@Override
	public CRUDDomainRepository<Subscription> getSubscriptionRepository() {
		return subscriptionRepository;
	}

	@Override
	public CRUDDomainRepository<UserRole> getUserRoleRepository() {
		return userRoleRepository;
	}

	@Override
	public List<UserRole> getUserRoles(User user) throws SQLException {
		List<UserRole> roles = new LinkedList<UserRole>();
		UserRole role;
		getUserRolesStatement.setLong(1, user.getId());
		ResultSet resultSet = getUserRolesStatement.executeQuery();
		while (resultSet.next()) {
			role = new UserRole();
			role.setId(resultSet.getLong(1));
			role.setName(resultSet.getString(2));
			roles.add(role);
		}
		
		return roles;
	}

	@Override
	public boolean processPayment(Subscription subscription) throws SQLException {
		if (!IsUnpayedSubscription(subscription)) {
			return false;
		}
		
		long paymentAmount = getPaymentAmount(subscription);
		processPayment.setLong(1, subscription.getId());
		processPayment.setLong(2, paymentAmount);
		processPayment.executeUpdate();
		return true;
	}

	@Override
	public boolean isLoginUnique(String login) throws SQLException {
		getUserByLoginStatement.setString(1, login);
		ResultSet resultSet = getUserByLoginStatement.executeQuery();
		return !resultSet.next();
	}

	@Override
	public void addPublicationToSubscription(Publication publication,
			Subscription subscription) throws SQLException {
		addPublicationToSubscription.setLong(1, publication.getId());
		addPublicationToSubscription.setLong(2, subscription.getId());
		addPublicationToSubscription.execute();
	}

	@Override
	public long getPredictedPaymentAmount(Collection<Publication> publications) throws SQLException {
		StringBuilder builder = new StringBuilder();
		Iterator<Publication> iterator = publications.iterator();
		while (iterator.hasNext()) {
			Publication publication = iterator.next();
			builder.append(publication.getId());
			if (iterator.hasNext()) {
				builder.append(", ");
			}
		}
		
		String sql = String.format(GET_PREDICTED_COST_SQL, builder.toString());
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			return resultSet.getLong(1);
		}
		
		return 0;
	}

	@Override
	public List<Subscription> getUnpayedSubscriptions() throws SQLException {
		List<Subscription> subscriptions = new LinkedList<Subscription>();
		ResultSet resultSet = getUnpayedSubscriptions.executeQuery();
		while (resultSet.next()) {
			Subscription subscription = new Subscription();
			subscription.setId(resultSet.getLong(1));
			subscription.setUserId(resultSet.getLong(2));
			subscriptions.add(subscription);
		}
		
		return subscriptions;
	}

	@Override
	public List<Publication> getSubscriptionPublications(Subscription subscription) throws SQLException {
		List<Publication> publications = new LinkedList<Publication>();
		getSubscriptionPublications.setLong(1, subscription.getId());
		ResultSet resultSet = getSubscriptionPublications.executeQuery();
		while (resultSet.next()) {
			Publication publication = new Publication();
			publication.setId(resultSet.getLong(1));
			publication.setName(resultSet.getString(2));
			publication.setPrice(resultSet.getLong(3));
			publications.add(publication);
		}
		
		return publications;
	}
	
	@Override
	public long getPaymentAmount(Subscription subscription) throws SQLException {
		getPaymentAmount.setLong(1, subscription.getId());
		ResultSet resultSet = getPaymentAmount.executeQuery();
		while (resultSet.next()) {
			return resultSet.getLong(1);
		}
		
		return 0;
	}

	@Override
	public void addRoleToUser(User user, UserRole role) throws SQLException {
		addRoleToUserStatement.setLong(1, user.getId());
		addRoleToUserStatement.setLong(2, role.getId());
		addRoleToUserStatement.execute();
	}

	@Override
	public User getUserByLogin(String login) throws SQLException {
		User user = null;
		getUserByLoginStatement.setString(1, login);
		ResultSet resultSet = getUserByLoginStatement.executeQuery();
		while (resultSet.next()) {
			user = new User();
			user.setId(resultSet.getLong(1));
			user.setLogin(resultSet.getString(2));
			user.setPassword(resultSet.getString(3));
			user.setSalt(resultSet.getString(4));
		}
		
		return user;
	}
	
	public boolean IsUnpayedSubscription(Subscription subscription) throws SQLException {
		subscriptionPaymentsCount.setLong(1, subscription.getId());
		ResultSet resultSet = subscriptionPaymentsCount.executeQuery();
		while (resultSet.next()) {
			long count = resultSet.getLong(1);
			return count == 0;
		}

		return true;
	}

}
